﻿// =================================================================== 
// 项目说明
//====================================================================
// 文件： ERP_OutBoundDetail.cs
// 创建时间：2016/12/2
// 负责人：
// ===================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CRMMis.Model;
using CRMMis.IDAL;


namespace MSSQLDAL
{
    /// <summary>
    /// 数据层实例化接口类  出库单详情表.
    /// </summary>
    public partial class ERP_OutBoundDetailDAL : IERP_OutBoundDetailDAL
    {
		//此处写查询数据的sql语句，


        //if QueryDataString==""，DAL层的GetList，GetRecordCount等函数将直接存取ERP_OutBoundDetail表


        //if QueryDataString!=""，则DAL层的GetList，GetRecordCount等函数将存取该SQL语句，取代表
		//注意：所写sql语句执行结果的字段必须是唯一的

		private const string QueryDataString = @"select a.*,
b.BatchNo,b.ProduceDate,b.DisinfectionDate,b.ExpiryDate,b.AsepticDate,
p.StoreRoomId,(select STOREROOM_NAME from VIEW_STORE where STOREROOM_ID = p.StoreRoomId) StoreRoomName,
d.Supplyprice,(d.Supplyprice * a.Count) TotalPrice,
e.CargoName,e.CargoCode,e.Spec,e.Version,e.Area,e.ProduceName,e.Unit,e.Spell,
f.Supplyprice CusSupplyprice,(f.Supplyprice * a.Count) CusTotalPrice,
g.CargoName CusCargoName,g.CargoCode CusCargoCode,g.Spell CusSpell
from ERP_OutBoundDetail a
inner join ERP_OutBound p on p.BoundId = a.DBoundId
inner join ERP_StockDetail b on b.ItemId = a.ItemId
inner join ERP_StockInfo c on c.StoreId = b.StoreId
inner join ERP_Cargo_Detail d on d.DetailId = c.DetailId
inner join ERP_Cargo e on e.CargoId = d.CargoId
inner join ERP_CusCargo_Detail f on f.DetailId = a.CusDetailId
inner join ERP_CusCargo g on g.CargoId = f.CargoId
";


        string QueryOutStoreDatilSqlStr = @"
select a.*,
b.State,b.BoundNo,b.DeliveryNo,
b.CheckDate,
b.StoreRoomId,
(select STOREROOM_NAME from VIEW_STORE where STOREROOM_ID=b.StoreRoomId ) STOREROOM_NAME,
b.CustomerId,
(select CustomerName from ERP_Customer where CustomerId=b.CustomerId ) CustomerName,
e.BatchNo,e.ProduceDate,e.DisinfectionDate,e.ExpiryDate,e.AsepticDate,
c.Supplyprice,c.Supplyprice*a.count SumSupplyprice,
d.CargoName,d.CargoCode,d.Spec,d.Version,d.ProduceName,d.Area,d.Unit
from ERP_OutBoundDetail a
inner join ERP_OutBound b on a.DBoundId = b.BoundId
inner join ERP_StockDetail e on a.ItemId = e.ItemId
inner join ERP_StockInfo f on e.StoreId = f.StoreId
inner join ERP_Cargo_Detail c on f.DetailId = c.DetailId
inner join ERP_Cargo d on c.CargoId = d.CargoId ";

        public DataTable QueryOutStoreDatil(int iPageSize, int iPageIndex, string sWhere, string sOder)
        {
            string tableStr = "(" + QueryOutStoreDatilSqlStr + ") SQL_A";
            if (sOder.Length <= 0)
                sOder = "ItemlId";
            string sqlStr = "select * from (select ROW_NUMBER() OVER(order by " + sOder + " ) as row, * from " + tableStr;
            if (sWhere.Length > 0)
                sqlStr += " where " + sWhere;
            sqlStr += ") SQL1 where row between " + Convert.ToString(iPageSize * (iPageIndex - 1) + 1) + " and " + Convert.ToString(iPageSize * iPageIndex);
            WriteLog("ERP_OutBoundDetailDAL", "QueryOutStoreDatil(int iPageSize,int iPageIndex, string sWhere, string sOder)", sqlStr);
            return SqlHelper.ExecuteDataset(Conn.SqlConnStr, CommandType.Text, sqlStr).Tables[0];
        }

        public int QueryOutStoreDatilRecorderCount(string sWhere)
        {
            string tableStr = "(" + QueryOutStoreDatilSqlStr + ") SQL_A";
            string sqlStr = "select Count(*) from " + tableStr;

            if (sWhere.Length > 0)
                sqlStr += " where " + sWhere;

            WriteLog("ERP_OutBoundDetailDAL", "QueryOutStoreDatilRecorderCount(string sWhere)", sqlStr);

            return Convert.ToInt32(SqlHelper.ExecuteScalar(Conn.SqlConnStr, CommandType.Text, sqlStr));
        }


    }
}
